
ALTER                 proc sp_create_update_adaptor_fg_lot (
 
  @part_number varchar(50), 
  @lot_number varchar(50), 
  @qty_in int,
  @qty_out int,
  @date_in datetime,
  @date_out datetime,
  @inventory_type varchar(50),
  @vendor_name varchar(50), 
  @date_code varchar(50),
  @vendor_lot varchar(500),
  @lot_comments varchar(1000),
  @status varchar(20),
  @mergedlot_number varchar(500)
  )
  as
  begin
  	declare @process_step_id int
  	declare @location_id int
  	declare @int int
  	declare @normal_cost float
  	declare @lot_id int
  	declare @this_lot_status varchar(50)
  	declare @date_time datetime
  	declare @start_date datetime
  	declare @end_date datetime
  	
 	if @status is null
 	begin
 		set @status = 'Running'
 	end 		 	
 
  	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name like @inventory_type and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
  	Select @location_id = Location_ID from Location where L_Type = @inventory_type and L_Vendor_Name = @vendor_name
  
  	if @process_step_id is null -- new Product or Product Name mismatch
  	begin
  		select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name like @inventory_type and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID and Product_Map_Table.Location_ID = @location_id
  		if @process_step_id is null
  		begin
  			exec sp_create_update_miscellaneous_lot @part_number, @lot_number, @vendor_lot,null, null, null,
  			@vendor_name, @inventory_type, 'Finished Goods', null, @date_in, @date_out, null, null, null, null, @qty_in, @qty_out, null, null, 'Running'
  
  			-- dummy update to avoid SQLException
  			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
  			return
  		end
  	end
  
  	if @process_step_id is not null
  	begin
  		update Miscellaneous_Lot_Table set Status = 'Close' where Part_Number = @part_number and Lot_Number = @lot_number and Vendor_Name = @vendor_name and PS_Type_Name like @inventory_type
  	end
  
  	Select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
  
  	exec sp_close_transit_lots_by_shippng_lot_id @process_step_id, @lot_number, @location_id, @qty_in, @date_in
  	
  	if @lot_id is null -- Check if the lot exists
  	begin -- Create Lot
  		select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
 
 		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Date_Code, Lot_Cost, Lot_Current_Status, Timestamp, Lot_Comments, Lot_Entered_By ) values (@process_step_id, @location_id, @lot_number, @date_code, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP, @lot_comments, 1)
  		select @lot_id = @@IDENTITY				
  	end	
  	else 
  	begin
  		update Lot_Table set Lot_Current_Status = @status, Lot_Date_Code = @date_code, Lot_Comments = @lot_comments , Lot_Entered_By = 1 where Lot_ID = @lot_id 
  	end
  	
 	-- insert/update Inventory Lot
  	declare @inv_lot_id int
  	declare @temp_varchar varchar(20)
  	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
  	set @start_date = convert(datetime, @temp_varchar)
  	set @temp_varchar = null
  	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
  	set @end_date = convert(datetime, @temp_varchar)
  	select @inv_lot_id = Lot_ID, @date_time = Timestamp from Inventory_Lot where lot_id = @lot_id
  	if @inv_lot_id is null
  	begin
  		Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, Timestamp, IL_Vendor_Lot_Number ,IL_Merged_Lot_Number ) values (@lot_id, @qty_in, @qty_out, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot, @mergedlot_number)
 
  		if @qty_out is not null
  		begin
  			update Lot_Table set Lot_Current_Status = 'Close', Lot_Comments = @lot_comments where Lot_ID = @lot_id
  			insert into Inventory_Lot_History(Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, Timestamp, IL_Current_Status, IL_Vendor_Lot_Number,IL_Merged_Lot_Number)
  			(select Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, Timestamp, 'Close', IL_Vendor_Lot_Number,IL_Merged_Lot_Number from Inventory_Lot where Lot_ID = @lot_id)
  		end
  	end
  	else
  	begin
  		declare @old_balance_qty int
  		declare @count_lot_id int
 
  		select @old_balance_qty = IL_Qty_Balance from Inventory_Lot where Lot_ID = @inv_lot_id
 
  		select @count_lot_id = ISNULL(sum(CSLNT_Qty_Pull),0) from customer_shipping_lot_number_table where cslnt_status is null and lot_id = @lot_id --Ravikanth on 5 Jan 2004
 
  		if @date_time is not null
  		begin
  			if @date_time between @start_date and @end_Date
  			begin
  				update Inventory_Lot set IL_Date_In = @date_in, IL_Qty_In = @qty_in, IL_Qty_Out = @qty_out, IL_Date_Out = @date_out, Timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot,IL_Merged_Lot_Number = @mergedlot_number where Lot_ID = @inv_lot_id
  			end
  			else
  			begin
  				update Inventory_Lot set IL_Date_In = @date_in, IL_Qty_In = @qty_in, IL_Qty_Out = @qty_out, IL_Date_Out = @date_out, Timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot,IL_Merged_Lot_Number = @mergedlot_number where Lot_ID = @inv_lot_id
  			end
  		end
  		else
  		begin
  			update Inventory_Lot set IL_Date_In = @date_in, IL_Qty_In = @qty_in, IL_Qty_Out = @qty_out, IL_Date_Out = @date_out, Timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot,IL_Merged_Lot_Number = @mergedlot_number where Lot_ID = @inv_lot_id
  		end
  		set @date_time = null
  
  		if @count_lot_id <> @qty_in
  		begin
  			update Inventory_Lot set IL_Qty_Balance = null, Timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  		end
  		else
  		begin
  			update Inventory_Lot set IL_Qty_Balance = @old_balance_qty, Timestamp = CURRENT_TIMESTAMP ,IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  		end
 
 		if @qty_out is not null
  		begin
  			update Lot_Table set Lot_Current_Status = 'Close', Lot_Comments = @lot_comments where Lot_ID = @lot_id
  			insert into Inventory_Lot_History(Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, Timestamp, IL_Current_Status, IL_Vendor_Lot_Number,IL_Merged_Lot_Number )
  			 (select Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, Timestamp, 'Close', IL_Vendor_Lot_Number,IL_Merged_Lot_Number from Inventory_Lot where Lot_ID = @lot_id)
  		end
  
  	end
  end

